﻿/*
 * Drops all database objects related to the SSAS Partition Manager in order to ensure a clean deploy
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */

USE [AdventureWorksDW2014];

PRINT 'Dropping all objects in the spm schema...';

DECLARE @name nvarchar(200);
DECLARE @schemaId int;
DECLARE @cmd nvarchar(1000);

DECLARE viewCursor CURSOR FOR
	SELECT [name], [schema_id] FROM sys.views WHERE schema_name([schema_id]) = N'spm';

OPEN viewCursor;
FETCH NEXT FROM viewCursor INTO @name, @schemaId;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'DROP VIEW [' + SCHEMA_NAME(@schemaId) + '].[' + @name + ']'
	PRINT @cmd;
	EXEC sp_executesql @cmd;
    FETCH NEXT FROM viewCursor INTO @name, @schemaId;
END

CLOSE viewCursor;
DEALLOCATE viewCursor;

DECLARE procedureCursor CURSOR FOR
	SELECT [name], [schema_id] FROM sys.procedures WHERE schema_name([schema_id]) = N'spm';

OPEN procedureCursor;
FETCH NEXT FROM procedureCursor INTO @name, @schemaId;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'DROP PROCEDURE [' + SCHEMA_NAME(@schemaId) + '].[' + @name + '];'
	PRINT @cmd;
	EXEC sp_executesql @cmd;
    FETCH NEXT FROM procedureCursor INTO @name, @schemaId;
END

CLOSE procedureCursor;
DEALLOCATE procedureCursor;

-- the tables have to be deleted in a specific order

IF object_id(N'spm.SsasMeasureGroup') is not null 
begin
	print 'Dropping table spm.SsasMeasureGroup';
	DROP TABLE spm.SsasMeasureGroup;
end

IF object_id(N'spm.SsasCube') is not null 
begin
	print 'Dropping table spm.SsasCube';
	DROP TABLE spm.SsasCube;
end

IF object_id(N'spm.SsasDatabase') is not null 
begin
	print 'Dropping table spm.SsasDatabase';
	DROP TABLE spm.SsasDatabase;
end

-- remove any other tables that are part of the SsasPartitionManager
DECLARE tableCursor CURSOR FOR
	SELECT [name], [schema_id] FROM sys.tables WHERE schema_name([schema_id]) = N'spm';

OPEN tableCursor;
FETCH NEXT FROM tableCursor INTO @name, @schemaId;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'DROP TABLE [' + SCHEMA_NAME(@schemaId) + '].[' + @name + ']'
	PRINT @cmd;
	EXEC sp_executesql @cmd;
    FETCH NEXT FROM tableCursor INTO @name, @schemaId;
END
CLOSE tableCursor;
DEALLOCATE tableCursor;

IF EXISTS(select 1 from sys.schemas where name = N'spm')
begin
	print 'Dropping SCHEMA spm';
	DROP SCHEMA [spm];
end
GO
